<?php
class Admin_Model_ScheduleMapper {
	protected $_dbTable;

	public function __construct() {

	}

	public function setDbTable($dbTable)
	{
		if (is_string($dbTable)) {
			$dbTable = new $dbTable();
		}
		if (!$dbTable instanceof Zend_Db_Table_Abstract) {
			throw new Exception('Invalid table data gateway provided');
		}
		$this->_dbTable = $dbTable;
		return $this;
	}

	public function getDbTable()
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Admin_Model_DbTable_Schedule');
		}
		return $this->_dbTable;
	}



	public function populateScheduleFromArray($array, Admin_Model_Schedule $scheduleModel) {
		$scheduleModel->_sid = $array['sid'];
		$scheduleModel->_fromdate = $array['fromdate'];
		$scheduleModel->_todate = $array['todate'];

		$scheduleModel->_mon = $array['mon'];
		$scheduleModel->_tue = $array['tue'];
		$scheduleModel->_wed = $array['web'];
		$scheduleModel->_thu = $array['thu'];
		$scheduleModel->_fri = $array['fri'];
		$scheduleModel->_sat = $array['sat'];
		$scheduleModel->_sun = $array['sun'];
	}

	public function populateScheduleFromRow(Zend_Db_Table_Row_Abstract $row, Admin_Model_Schedule $scheduleModel) {
		$scheduleModel->_sid = $row->sid;
		$scheduleModel->_fromdate = $row->fromdate;
		$scheduleModel->_todate = $row->todate;

		$scheduleModel->_mon = $row->mon;
		$scheduleModel->_tue = $row->tue;
		$scheduleModel->_wed = $row->wed;
		$scheduleModel->_thu = $row->thu;
		$scheduleModel->_fri = $row->fri;
		$scheduleModel->_sat = $row->sat;
		$scheduleModel->_sun = $row->sun;
	}


	public function save(Admin_Model_Schedule $scheduleInfo)
	{
		$sid = $scheduleInfo->_sid;
		$data = array(
			'sid' => $scheduleInfo->_sid,
            'fromdate'   => $scheduleInfo->_fromdate,
            'todate' => $scheduleInfo->_todate,
			'mon' => $scheduleInfo->_mon,
			'tue' => $scheduleInfo->_tue,
			'wed' => $scheduleInfo->_wed,
			'thu' => $scheduleInfo->_thu,
			'fri' => $scheduleInfo->_fri,
			'sat' => $scheduleInfo->_sat,
			'sun' => $scheduleInfo->_sun
		);

		if (!isset($sid)) {
			unset($data['sid']);
			$this->getDbTable()->insert($data);
		} else {
			$this->getDbTable()->update($data, array('sid = ?' => $sid));
		}
	}

	public function find($sid, Admin_Model_Schedule $scheduleInfo)
	{
		$result = $this->getDbTable()->find($sid);
		if (0 == count($result)) {
			return;
		}
		$row = $result->current();
		$this->populateScheduleFromRow($row, $scheduleInfo);
		return $scheduleInfo;
	}
	
	public function checkDuplicatePeriod($fromdate, $todate) {
		$duplicate = false;
		$result = $this->getDbTable()->fetchAll(array('fromdate = ?' => $fromdate, 'todate = ?' => $todate));
		if (0 < count($result)) {
			$duplicate = true;
		}
		return $duplicate;
	}

	public function fetchAll()
	{
		$resultSet = $this->getDbTable()->fetchAll();
		$entries = array();
		if (0 == count($resultSet)) {
			return;
		}
		foreach ($resultSet as $row) {
			$entry = new Admin_Model_Schedule();
			$this->populateScheduleFromRow($row, $entry);
			$entries[] = $entry;
		}
		return $entries;
	}

	public function delete($sid)
	{
		$result = $this->getDbTable()->find($sid);
		if (0 == count($result)) {
			return;
		}
		return $this->getDbTable()->delete(array('sid = ?' => $sid));
	}
	
	public function findmenu($sid){
		$arrMenu = array();
		$schedule = new Admin_Model_Schedule();
		$hasshedule = $this->find($sid, $schedule);
		if(isset($hasshedule)){
			$menuMapper = new Front_Model_MenuMapper();
			
			$monMid = $schedule->_mon;
			if(isset($monMid)) {
				$menuMon = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($monMid, $menuMon);
				if (isset($hasMenu)) {
					$arrMenu['Mon'] = $menuMon;
				}
			}
			
			$tueMid = $schedule->_tue;
			if(isset($tueMid)) {
				$menuTue = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($tueMid, $menuTue);
				if (isset($hasMenu)) {
					$arrMenu['Tue'] = $menuTue;
				}
			}
			
			$wedMid = $schedule->_wed;
			if(isset($wedMid)) {
				$menuWed = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($wedMid, $menuWed);
				if (isset($hasMenu)) {
					$arrMenu['Wed'] = $menuWed;
				}
			}
			
			$thuMid = $schedule->_thu;
			if(isset($thuMid)) {
				$menuThu = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($thuMid, $menuThu);
				if (isset($hasMenu)) {
					$arrMenu['Thu'] = $menuThu;
				}
			}
			
			$friMid = $schedule->_fri;
			if(isset($friMid)) {
				$menuFri = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($friMid, $menuFri);
				if (isset($hasMenu)) {
					$arrMenu['Fri'] = $menuFri;
				}
			}
			
			$satMid = $schedule->_sat;
			if(isset($satMid)) {
				$menuSat = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($satMid, $menuSat);
				if (isset($hasMenu)) {
					$arrMenu['Sat'] = $menuSat;
				}
			}
			
			$sunMid = $schedule->_sun;
			if(isset($sunMid)) {
				$menuSun = new Front_Model_Menu();
				$hasMenu = $menuMapper->find($sunMid, $menuSun);
				if (isset($hasMenu)) {
					$arrMenu['Sun'] = $menuSun;
				}
			}
		}
		return $arrMenu;
	}
	
	public function createSql($date){
		$sql = "
		select 
				acc.address	as address,
				type.typename as typename
		,		dat.*		
		from
				`account`  as acc,
				`type` as type
		,		(
					select
							dat.date as date
					,		dat.uid as uid
					,		dat.type as type
					,		sum(dat.quantity) as quantity
					,		max(dat.menuname) as menuname
					from
							(
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 0 DAY) = '%date%'
								and		che.mon = men.mid
								union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 1 DAY) = '%date%'
								and		che.tue = men.mid
											union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 2 DAY) = '%date%'
								and		che.wed = men.mid
											union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 3 DAY) = '%date%'
								and		che.thu  = men.mid
											union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 4 DAY) = '%date%'
								and		che.fri  = men.mid			
								union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 5 DAY) = '%date%'
								and		che.sat  = men.mid
											union all
								select
										men.menuname as menuname
								,		men.mid 	 as mid
								,		ord.sid		 as sid
								,		ord.type	 as type
								,		ord.date	 as date
								,		ord.uid		 as uid
								,		ord.quantity		 as quantity
								
								from
										`schedule` as che
								,		`menu`	 as men
								,		`order`	 as ord
								where
										   ord.date = '%date%'
								and		ord.sid = che.sid
								and		DATE_ADD(che.fromdate, INTERVAL 6 DAY) = '%date%'
								and		che.sun  = men.mid
							) as dat
						group by
								dat.date
						,		dat.uid
						,		dat.type	 
				) as dat
		where
				dat.uid = acc.uid 
				and dat.type = type.tid
		order by 
				acc.address
		,		dat.menuname
		";
		$sql = str_replace("%date%", $date , $sql);
		return $sql;
	}
	public function getStatistics($date){
		$sql = $this->createSql($date);
	    $adapter = $this->getDbTable()->getAdapter();
	    $resultSet = $adapter->query($sql);
	    $redata = array();
		while ($row = $resultSet->fetch()) {
		   $redata[] = $row;
		}
		if(empty($redata)){
			return;
		}
    	return $redata;
	}
}